VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "clsDirectives"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = True
Option Explicit

Public Function ValidateDirectiveDelete(ByVal sUserName As String, ByVal nReqSeqNum As String) As String

    Dim cnValidateDirectiveDelete As ADODB.Connection
    
    Dim rsHolder As ADODB.recordSet
    Dim cmd
    
    Dim sMessage
    
    Set cnValidateDirectiveDelete = New ADODB.Connection
    
    
    Set cmd = New ADODB.Command 'CreateObject("ADODB.COMMAND")
    
    With cnValidateDirectiveDelete
        .ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
        .Open
    End With
    
    Set rsHolder = New ADODB.recordSet
    
    '-------------------------------------------
    
    cmd.CommandText = "dir_validate_delete_request"
    cmd.CommandType = adCmdStoredProc
    Set cmd.ActiveConnection = cnValidateDirectiveDelete
    
        cmd.Parameters.Append cmd.CreateParameter("username", adVarChar, adParamInput, 10, sUserName)
        cmd.Parameters.Append cmd.CreateParameter("req_seq_num", adInteger, adParamInput, , CLng(nReqSeqNum))
        
    Set rsHolder = cmd.Execute
    
    If rsHolder("allowed_yn") = "N" Then
        sMessage = rsHolder("Message").Value
    Else
        sMessage = ""
    End If
    
    If Not (rsHolder Is Nothing) Then
      ValidateDirectiveDelete = sMessage
    Else
      ValidateDirectiveDelete = ""
    End If
    
PROC_EXIT:
  
  Exit Function
  
PROC_ERROR:
  
  Err.Raise Err.Number, "ValidateDirecteDelete", _
            "The information could not be validated." & vbCrLf & _
            "Description: " & Err.Description
  GoTo PROC_EXIT


cnValidateDirectiveDelete.Close
Set cnValidateDirectiveDelete = Nothing


rsHolder.Close
Set rsHolder = Nothing
Set cmd = Nothing
    
    
End Function

Public Function GetDirectiveDetails(ByVal nReqSeqNum, _
                                    ByRef sTaxPayer, _
                                    ByRef nIncomeTaxReferenceNo, _
                                    ByRef nFundMembershipNo, _
                                    ByRef nEmployeeNumber, _
                                    ByRef nTaxYear, _
                                    ByRef nLumpSum, _
                                    ByRef nEmpID, _
                                    ByRef nFundID, _
                                    ByRef sRequestReason)

    Dim cnGetDirectiveDetails As ADODB.Connection
    
    Dim rsHolder As ADODB.recordSet
    Dim cmd
    
    Dim sMessage
    
    Set cnGetDirectiveDetails = New ADODB.Connection
    
    
    Set cmd = New ADODB.Command 'CreateObject("ADODB.COMMAND")
    
    With cnGetDirectiveDetails
        .ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
        .Open
    End With
    
    Set rsHolder = New ADODB.recordSet
    
    '-------------------------------------------
    
    cmd.CommandText = "dir_get_request_details"
    cmd.CommandType = adCmdStoredProc
    Set cmd.ActiveConnection = cnGetDirectiveDetails
    
        cmd.Parameters.Append cmd.CreateParameter("req_seq_num", adDouble, adParamInput, , nReqSeqNum)
        
    Set rsHolder = cmd.Execute
    
    If Not (rsHolder Is Nothing) Then
    
        sTaxPayer = rsHolder("TaxPayer")
        sRequestReason = rsHolder("req_reason")
        
        'all numeric values must be tested to see if they are nulls, and if so, be set to ""
        nIncomeTaxReferenceNo = CStr(IIf(IsNull(rsHolder("income_tax_reference_number")), "", rsHolder("income_tax_reference_number")))
        nFundMembershipNo = CStr(IIf(IsNull(rsHolder("fund_membership_number")), "", rsHolder("fund_membership_number")))
        nEmployeeNumber = CStr(IIf(IsNull(rsHolder("employee_number")), "", rsHolder("employee_number")))
        nTaxYear = CStr(IIf(IsNull(rsHolder("tax_year")), "", rsHolder("tax_year")))
        nLumpSum = CStr(IIf(IsNull(rsHolder("gross_lump_sum")), "", rsHolder("gross_lump_sum")))
        nEmpID = CStr(IIf(IsNull(rsHolder("emp_id")), "", rsHolder("emp_id")))
        nFundID = CStr(IIf(IsNull(rsHolder("fun_id")), "", rsHolder("fun_id")))
        
        'if the income tax reference number was captured as 0, make it ""
        If nIncomeTaxReferenceNo = "0" Then
            nIncomeTaxReferenceNo = ""
        End If
        
    Else
      GetDirectiveDetails = ""
    End If
    
PROC_EXIT:
  
  Exit Function
  
PROC_ERROR:
  
  Err.Raise Err.Number, "GetDirectiveDetails", _
            "The information could not be retrieved." & vbCrLf & _
            "Description: " & Err.Description
  GoTo PROC_EXIT


cnGetDirectiveDetails.Close
Set cnGetDirectiveDetails = Nothing


rsHolder.Close
Set rsHolder = Nothing
Set cmd = Nothing

End Function

Public Function DeleteDirective(ByVal nReqSeqNum As String) As String

    Dim cnDeleteDirective As ADODB.Connection
    
    Dim rsHolder As ADODB.recordSet
    Dim cmd
    
    Dim sMessage
    
    Set cnDeleteDirective = New ADODB.Connection
    
    
    Set cmd = New ADODB.Command 'CreateObject("ADODB.COMMAND")
    
    With cnDeleteDirective
        .ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
        .Open
    End With
    
    Set rsHolder = New ADODB.recordSet
    
    '-------------------------------------------
    
    cmd.CommandText = "dir_del_request"
    cmd.CommandType = adCmdStoredProc
    Set cmd.ActiveConnection = cnDeleteDirective
    
        cmd.Parameters.Append cmd.CreateParameter("req_seq_num", adDouble, adParamInput, , nReqSeqNum)
        
    Set rsHolder = cmd.Execute
    
PROC_EXIT:
  
  Exit Function
  
PROC_ERROR:
  DeleteDirective = "An error occurred."
  Err.Raise Err.Number, "DeleteDirective", _
            "The directive could not be deleted." & vbCrLf & _
            "Description: " & Err.Description
  GoTo PROC_EXIT


cnDeleteDirective.Close
Set cnDeleteDirective = Nothing


rsHolder.Close
Set rsHolder = Nothing
Set cmd = Nothing

End Function

'*-----------------------------------------------------------------------
'* Method:      ValidateDirectiveCancel
'* Parameters:  sUsername - The logged on user's username
'*              nDirectiveNumber - The directive number that needs to be checked.
'* Returns:     sCancelYN - can the directive be cancelled, "Y" if it can, "N" if it can't.
'*              sCancelMessage - reason why it can't be cancelled, if "N"
'* Description: Validates if a directive can be cancelled.
'*-----------------------------------------------------------------------

Public Function ValidateDirectiveCancel(ByVal sUserName As String, ByVal nDirectiveNumber As String, ByRef sCancelYN As String, ByRef sCancelMessage As String) As String

Dim cnDBaseConnection As ADODB.Connection
Dim cmd As New ADODB.Command
Dim rsValidateDirectiveCancel As New ADODB.recordSet

With cnDBaseConnection
    .ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
    .Open
End With


' Validate whether the user has rights to delete this directive
Set cmd.ActiveConnection = cnDBaseConnection
    cmd.CommandText = "dir_validate_cancel"
    cmd.CommandType = adCmdStoredProc
    cmd.Parameters.Append cmd.CreateParameter("username", adVarChar, adParamInput, 10, sUserName)
    cmd.Parameters.Append cmd.CreateParameter("directive_number", adDouble, adParamInput, , nDirectiveNumber)

Set rsValidateDirectiveCancel = cmd.Execute
        
sCancelYN = rsValidateDirectiveCancel("allowed_yn")

If sCancelYN = "N" Then
    sCancelMessage = rsValidateDirectiveCancel("message")
End If
    
End Function


Public Function GetDirectiveDetailsRS(ByVal nReqNumber As String) As recordSet

On Error GoTo PROC_ERROR

    Dim cnConnection As ADODB.Connection
    Dim rsRecordSet As ADODB.recordSet
    
    
    Dim cmd As ADODB.Command
    
    
    
    Set cnConnection = New ADODB.Connection
    
    With cnConnection
        .CursorLocation = adUseClient
        .IsolationLevel = adXactBrowse
        .ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
        .Open
    End With
    
    Set rsRecordSet = New ADODB.recordSet
        rsRecordSet.CursorLocation = adUseClient
        rsRecordSet.CursorType = adOpenStatic
        
    Set cmd = New ADODB.Command
    cmd.CommandText = "dir_get_request_details"
    cmd.CommandType = adCmdStoredProc
    cmd.ActiveConnection = cnConnection
    
        cmd.Parameters.Append cmd.CreateParameter("req_seq_num", adDouble, adParamInput, , nReqNumber)
        
    Set rsRecordSet = cmd.Execute
    
    Set rsRecordSet.ActiveConnection = Nothing
    
    Set GetDirectiveDetailsRS = rsRecordSet
    
    
PROC_EXIT:
  
  cnConnection.Close
    Set cnConnection = Nothing
  
  Exit Function
  
PROC_ERROR:
  Err.Raise Err.Number, "GetDirectiveDetailsRS", _
            "Information could not be retrieved." & vbCrLf & _
            "Description: " & Err.Description
  GoTo PROC_EXIT
  
End Function
Public Function DeleteRequest(ByVal nReqSeqNum As String) As String

    Dim cnDeleteDirective As ADODB.Connection
    
    Dim rsHolder As ADODB.recordSet
    Dim cmd
    
    Dim sMessage
    
    Set cnDeleteDirective = New ADODB.Connection
    
    
    Set cmd = New ADODB.Command 'CreateObject("ADODB.COMMAND")
    
    With cnDeleteDirective
        .ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
        .Open
    End With
    
    Set rsHolder = New ADODB.recordSet
    
    '-------------------------------------------
    
    cmd.CommandText = "dir_del_directive_request"
    cmd.CommandType = adCmdStoredProc
    Set cmd.ActiveConnection = cnDeleteDirective
    
        cmd.Parameters.Append cmd.CreateParameter("req_seq_num", adDouble, adParamInput, , nReqSeqNum)
        
    Set rsHolder = cmd.Execute
    
PROC_EXIT:
  
  Exit Function
  
PROC_ERROR:
  DeleteRequest = "An error occurred."
  Err.Raise Err.Number, "DeleteRequest", _
            "The directive could not be deleted." & vbCrLf & _
            "Description: " & Err.Description
  GoTo PROC_EXIT


cnDeleteDirective.Close
Set cnDeleteDirective = Nothing


rsHolder.Close
Set rsHolder = Nothing
Set cmd = Nothing

End Function

Public Function CheckInProgress() As String

    Dim cnCheckInProgress As ADODB.Connection
    Dim adoComm As ADODB.Command
    Dim rsHolder As ADODB.recordSet
    
    Dim strSQL As String
    
    
    Dim sMessage
    
    Set cnCheckInProgress = New ADODB.Connection
    
    
    
    With cnCheckInProgress
        .ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
        .Open
    End With
    
    Set rsHolder = New ADODB.recordSet
    
    strSQL = "select var_value from dir_parameters where code = 'Extract_In_Progress' "
    
    Set adoComm = New ADODB.Command
    With adoComm
        .CommandText = strSQL
        .CommandType = adCmdText
        Set .ActiveConnection = cnCheckInProgress
    End With
    
    Set rsHolder = adoComm.Execute
    
    CheckInProgress = rsHolder.Fields("var_value").Value
    
PROC_EXIT:
  
  Exit Function
  
PROC_ERROR:
  CheckInProgress = "An error occurred."
  Err.Raise Err.Number, "CheckInProgress", _
            "The directive could not be deleted." & vbCrLf & _
            "Description: " & Err.Description
  GoTo PROC_EXIT

'Clean up
cnCheckInProgress.Close
Set cnCheckInProgress = Nothing

rsHolder.Close
Set rsHolder = Nothing
Set adoComm = Nothing

End Function
